{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "ddfea6f2",
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.sql import SparkSession\n",
    "from pyspark.sql.functions import lit\n",
    "from pyspark.sql.types import StructType, StructField, StringType, IntegerType\n",
    "\n",
    "# 创建SparkSession\n",
    "spark = SparkSession.builder.appName(\"AddDateColumn\").getOrCreate()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "56845172-7efb-41b4-ac7f-78e017a11a5d",
   "metadata": {},
   "outputs": [],
   "source": [
    "df=spark.read.parquet('/Users/dob/develop/tmp/data/result/tbl_click/dt=2025072300')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "3f9fdebc-5595-47b3-a7e6-ad5453a9e14c",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "25/07/30 14:33:45 WARN package: Truncated the string representation of a plan since it was too large. This behavior can be adjusted by setting 'spark.sql.debug.maxToStringFields'.\n",
      "[Stage 2:>                                                          (0 + 1) / 1]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---------+-----------+--------------+-------------+-------+------------+---------+------------+------+-------+-------------------+--------------------+--------------------+--------------------+--------------------+-------+-------+----------+--------------------+--------+-------+--------+----------+-------------+---------+--------+-------+-------+----------+-----------+---------------+-----------+-----------+--------+-------+------+--------------------+-------+----+---------+-----------+--------------------+------+------+-------+---------+-------+----------+---------+---------+------+-----------+---------+------+--------+--------------------+------------+-------------+---------+----------+--------+-------+--------------------+--------------------+--------------------+-------------+---------------+----------+----------+------------------+-----------------------+-------+-------+-----------+----------+----------+-------------+----------+-----------+--------------------+-----------+-------------+------------------+----------+\n",
      "|accountId|action_type|actualBidPrice|actualRevenue|adClass|adClickCount|adGroupId|adPositionId|adnRid|adsetId|algo_random_traffic|       apkSelectInfo|               appId|              appKey|             appName|appType| appVer|appVersion|             beylaId|bidPrice|bidType|blockIds|campaignId|clickUrlIndex|companyId|configId|country|cpu_bit|creativeId|displayType|         dspCId|  dspCampId|    dspName|dspPosId|dspType|dsp_id|        dsp_name_new|   ecpm| env|eventTime|flow_status|                gaid|gameId|gbCode|geoCode|geoCodeIp|gp_type|isNewCache|isOffline|isOneshot|is_dpa|is_internal|is_new_ad|itemId|loadType|        manufacturer|midasVersion|new_ad_action|objective|oem_pos_id|offer_id|orderId|           otherInfo|         packageName|          pageviewId|  processTime|recordEventTime|req_source|  req_time|requestChannelType|resourceFreqControlMode|revenue| sdkVer|strBlockIds|strategyId|targetGame|targetKeyword|targetRoom|targetScene|          test_infos|trafficType|traffic_level|               uid|        dt|\n",
      "+---------+-----------+--------------+-------------+-------+------------+---------+------------+------+-------+-------------------+--------------------+--------------------+--------------------+--------------------+-------+-------+----------+--------------------+--------+-------+--------+----------+-------------+---------+--------+-------+-------+----------+-----------+---------------+-----------+-----------+--------+-------+------+--------------------+-------+----+---------+-----------+--------------------+------+------+-------+---------+-------+----------+---------+---------+------+-----------+---------+------+--------+--------------------+------------+-------------+---------+----------+--------+-------+--------------------+--------------------+--------------------+-------------+---------------+----------+----------+------------------+-----------------------+-------+-------+-----------+----------+----------+-------------+----------+-----------+--------------------+-----------+-------------+------------------+----------+\n",
      "|        0|          0|             0|            0|      0|           1|        0|        3843|      |   null|                  0|                  {}|com.lenovo.anysha...|5a0bac80-6060-41e...|com.lenovo.anysha...|      1|4062588|          |d9dd2c338eba4824a...|       0|      0|     [0]|      null|            1|        0|       0|       |       |      null|          0|        3745772|     337749|ad_exchange|    3843|   1006|  null|ad_exchange:pubna...|1148000|prod|        0|          0|32179f1e-df48-4fb...|      |      |     co|       co|      1|      null|        0|     null|     0|          0|     null|     0|       0|              Xiaomi|         2.0|            0|        0|          |    null|   null|                  {}|                null|          XJByxCTPwl|1752451200000|  1752451200000|         0|         0|                 0|                      0|      0|4060041|          0|      null|      null|         null|      null|       null|[{\"test_id\": \"254...|          0|            3|                  |2025072300|\n",
      "|        0|          7|             0|            0|      0|           1|  1840694|        3507|      |  24038|                  0|{\"strategy_id\": 1...|com.lenovo.anysha...|5a0bac80-6060-41e...|com.lenovo.anysha...|      1|4065098|          |6ff03894bfee485c8...|  190000|      4|     [0]|     17871|         null|        0|       0|     mr|     64|  23513547|          0|               |          0|       null|    null|   1009|  null|               midas|  46568|prod|        0|          1|13f6fce3-3c7a-434...|      |      |     mr|       mr|      2|      null|        1|     null|     0|          0|        0|     0|       0|             samsung|         2.0|            0|        2|          |        |      0|{auto_active -> 0...|      com.lawhatsapp|          ojffiAtiAR|1752451200000|  1752451200000|         0|1752446645|                 4|                      0|      0|4060043|          0|         0|      null|         null|      null|       null|[{\"test_id\": \"254...|          2|            2|a.2512cf66bc81481d|2025072300|\n",
      "|        0|          1|             0|            0|      0|           1|  1844343|        2955|      |  26890|                  0|{\"strategy_id\": 1...|com.lenovo.anysha...|5a0bac80-6060-41e...|com.lenovo.anysha...|      1|4062328|          |29383b34ddf145c88...|   30000|      4|     [0]|     20494|         null|        0|       0|     gy|     32|  23565956|          0|               |          0|       null|    null|   1009|  null|               midas|     45|prod|        0|          1|                    |      |      |     gy|       gy|      1|      null|        1|     null|     0|          0|        0|     0|       0|              Amazon|         2.0|            0|        2|          |        |      0|{auto_active -> 0...|solitaire.puzzle....|5ee5602e-09f4-41a...|1752451200000|  1752451200000|         0|1752340024|                 4|                      0|      0|4060024|          0|         0|      null|         null|      null|       null|[{\"test_id\": \"254...|          2|            2|                  |2025072300|\n",
      "|        0|          1|             0|            0|      0|           1|  1847217|         408|      |  28998|                  0|                  {}|com.lenovo.anysha...|5a0bac80-6060-41e...|com.lenovo.anysha...|      1|4062599|          |a9bf5cc7b59740308...|  800000|      4|     [0]|     22368|         null|        0|       0|     ph|     64|  23595786|          0|               |          0|       null|    null|   1009|  null|               midas| 800000|prod|        0|          1|739d1def-348b-4d0...|      |      |     ph|       ph|      1|      null|        0|     null|     0|          0|        4|     0|       0|                vivo|         2.0|            0|        2|          |        |      0|{auto_active -> 1...|app.casinopluscol...|d89b6e96-852b-458...|1752451200000|  1752451200000|         0|1752451198|                 4|                      0|      0|4060045|          0|         7|      null|         null|      null|       null|[{\"test_id\": \"254...|          1|            1|                  |2025072300|\n",
      "|        0|          3|             0|            0|      0|           1|  1837575|        2955|      |  21883|                  0|                  {}|com.lenovo.anysha...|5a0bac80-6060-41e...|com.lenovo.anysha...|      1|4062588|          |a367eac8aafa47ae9...|     700|      3|     [0]|     15890|         null|        0|       0|     ni|     32|  23560627|          0|               |          0|       null|    null|   1009|  null|               midas|  70101|prod|        0|          1|20204d86-72e6-4b1...|      |      |     ni|       ni|      1|      null|        0|     null|     0|          0|        4|     0|       0|             samsung|         2.0|            0|        1|          |        |      0|  {auto_active -> 0}|                null|14ff3986-3c16-4da...|1752451200000|  1752451200000|         0|1752451187|                 4|                      0|7000000|4060041|          0|         0|      null|         null|      null|       null|[{\"test_id\": \"254...|          1|            2|                  |2025072300|\n",
      "|        0|          3|             0|            0|      0|           1|  1841562|         401|      |  24565|                  0|                  {}|com.lenovo.anysha...|5a0bac80-6060-41e...|com.lenovo.anysha...|      1|4062049|          |5ede77d6f5224043a...|     600|      3|     [0]|     18354|         null|        0|       0|     bt|     64|  23540737|          0|               |          0|       null|    null|   1009|  null|               midas| 149037| pre|        0|          1|3316fdef-3519-495...|      |      |     bt|       bt|      1|      null|        0|     null|     0|          0|        4|     0|       0|                vivo|         2.0|            0|        1|          |        |      0|  {auto_active -> 0}|                null|a5c495b0-bc2d-48c...|1752451200000|  1752451200000|         0|1752451196|                 4|                      0|6000000|4060006|          0|         0|      null|         null|      null|       null|[{\"test_id\": \"254...|          1|            2|    m.0c6046b46f2c|2025072300|\n",
      "|        0|          7|             0|            0|      0|           1|  1838305|         408|      |  22403|                  0|{\"strategy_id\": 1...|com.lenovo.anysha...|5a0bac80-6060-41e...|com.lenovo.anysha...|      1|4063348|          |3db6645c2eda46d78...|  190000|      4|     [0]|     16380|         null|        0|       0|     sy|     32|  23513562|          0|               |          0|       null|    null|   1009|  null|               midas|   3138|prod|        0|          1|18e728be-d27f-496...|      |      |     sy|       sy|      2|      null|        1|     null|     0|          0|        0|     0|       0|             samsung|         2.0|            0|        2|          |        |      0|{auto_active -> 0...|             com.lmt|cd02621e-8903-491...|1752451200000|  1752451200000|         0|1752432072|                 4|                      0|      0|4060025|          0|         1|      null|         null|      null|       null|[{\"test_id\": \"254...|          2|            2| a.f32d698c33383a6|2025072300|\n",
      "|        0|          7|             0|            0|      0|           1|  1846860|        3843|      |  28685|                  0|{\"strategy_id\": 1...|com.lenovo.anysha...|5a0bac80-6060-41e...|com.lenovo.anysha...|      1|4065028|          |4a69fcec74aa4f61b...|  700000|      4|     [0]|     22092|         null|        0|       0|     ph|     64|  23591660|          0|               |          0|       null|    null|   1009|  null|               midas|    972|prod|        0|          1|e1879be0-d01e-4c8...|      |      |     ph|       ph|      2|      null|        1|     null|     0|          0|        0|     0|       0|              realme|         2.0|            0|        2|          |        |      0|{auto_active -> 0...|    com.okgames.game|          ocDEtgJIyd|1752451200000|  1752451200000|         0|1752321717|                 4|                      0|      0|4060043|          0|         0|      null|         null|      null|       null|[{\"test_id\": \"254...|          2|            1|a.7fe551f8800725a0|2025072300|\n",
      "|        0|          7|             0|            0|      0|           1|  1838103|        3139|      |  22266|                  0|{\"strategy_id\": 1...|com.lenovo.anysha...|5a0bac80-6060-41e...|com.lenovo.anysha...|      1|4063448|          |8ce7accd10d94a509...| 1700000|      4|     [0]|     16247|         null|        0|       0|     eg|     64|  23466415|          0|               |          0|       null|    null|   1009|  null|               midas|  15586|prod|        0|          1|ea7f1456-9458-459...|      |      |     eg|       eg|      2|      null|        1|     null|     0|          0|        0|     0|       0|                OPPO|         2.0|            0|        2|          |        |      0|{auto_active -> 0...|    org.xbet.client1|efcebe8e-1f20-4da...|1752451201000|  1752451201000|         0|1748800200|                 4|                      0|      0|4060029|          0|         1|      null|         null|      null|       null|[{\"test_id\": \"254...|          2|            1|a.ee3544fcc811e65f|2025072300|\n",
      "|        0|          1|             0|            0|      0|           1|  1844343|        3496|      |  26890|                  0|{\"strategy_id\": 1...|com.lenovo.anysha...|5a0bac80-6060-41e...|com.lenovo.anysha...|      1|4062508|          |32e3203569cd48519...|   10000|      4|     [0]|     20494|         null|        0|       0|     bo|     32|  23565946|          0|               |          0|       null|    null|   1009|  null|               midas|      8|prod|        0|          1|35c3361b-ccbd-473...|      |      |     bo|       bo|      1|      null|        1|     null|     0|          0|        0|     0|       0|             samsung|         2.0|            0|        2|          |        |      0|{auto_active -> 0...|solitaire.puzzle....|          qiWXtUiuCq|1752451201000|  1752451201000|         0|1750375639|                 4|                      0|      0|4060041|          0|         0|      null|         null|      null|       null|[{\"test_id\": \"254...|          2|            2|                  |2025072300|\n",
      "|        0|          7|             0|            0|      0|           1|  1842984|        3144|      |  25716|                  0|{\"strategy_id\": 1...|com.lenovo.anysha...|5a0bac80-6060-41e...|com.lenovo.anysha...|      1|4063392|          |4f3dd84930884e518...|  330000|      4|     [0]|     19435|         null|        0|       0|     za|     32|  23543639|          0|               |          0|       null|    null|   1009|  null|               midas|  51466| pre|        0|          1|a4759ac2-e9f2-473...|      |      |     za|       za|      2|      null|        1|     null|     0|          0|        0|     0|       0|                ITEL|         2.0|            0|        2|          |        |      0|{auto_active -> 0...|com.music.downloa...|d377cc8d-dc14-475...|1752451201000|  1752451201000|         0|1745939891|                 4|                      0|      0|4060029|          0|         1|      null|         null|      null|       null|[{\"test_id\": \"254...|          2|            1|a.ca3325480e24c6ad|2025072300|\n",
      "|        0|          7|             0|            0|      0|           1|  1847292|        3507|      |  29087|                  0|{\"strategy_id\": 1...|com.lenovo.anysha...|5a0bac80-6060-41e...|com.lenovo.anysha...|      1|4065048|          |da7058de047b42008...|  130000|      4|     [0]|     22445|         null|        0|       0|     eg|     64|  23555569|          0|               |          0|       null|    null|   1009|  null|               midas|  44051| pre|        0|          1|32516942-0c02-402...|      |      |     eg|       eg|      2|      null|        1|     null|     0|          0|        0|     0|       0|                OPPO|         2.0|            0|        2|          |        |      0|{auto_active -> 0...|               wa.yo|          ssrwtcclGK|1752451201000|  1752451201000|         0|1752156650|                 4|                      0|      0|4060043|          0|         0|      null|         null|      null|       null|[{\"test_id\": \"254...|          2|            1|a.5b9c39845b7f2a63|2025072300|\n",
      "|        0|          1|             0|            0|      0|           1|  1843044|        2957|      |  25716|                  0|{\"strategy_id\": 1...|        shareit.lite|1108ef26-7ddb-461...|        shareit.lite|      1|5030788|          |86968be7437748f08...|  330000|      4|     [0]|     19435|         null|        0|       0|     za|     64|  23552017|          1|               |          0|       null|    null|   1009|  null|               midas|   2078|prod|        0|          1|c373c2ed-2801-42f...|      |      |     za|       za|      1|      null|        1|     null|     0|          0|        0|     0|       0|                OPPO|         2.0|            0|        2|          |        |      0|{auto_active -> 0...|com.music.downloa...|          wMZPWDZgZd|1752451201000|  1752451201000|         0|1746748936|                 4|                      0|      0|4060043|          0|         0|      null|         null|      null|       null|[{\"test_id\": \"254...|          2|            1|                  |2025072300|\n",
      "|        0|          0|             0|            0|      0|           1|        0|        3835|      |   null|                  0|                  {}|com.lenovo.anysha...|5a0bac80-6060-41e...|com.lenovo.anysha...|      1|4062588|          |f4dc1648125548eb8...|       0|      0|     [0]|      null|            1|        0|       0|       |       |      null|          0|1_212527612_0x0|1_212527612|ad_exchange|    3835|   1006|  null|  ad_exchange:yandex|1267752|prod|        0|          0|76de5957-28bb-485...|      |      |     ru|       ru|      1|      null|        0|     null|     0|          0|     null|     0|       0|             samsung|         2.0|            0|        0|          |    null|   null|                  {}|                null|          CrRDCwqlJL|1752451201000|  1752451201000|         0|         0|                 0|                      0|      0|4060041|          0|      null|      null|         null|      null|       null|[{\"test_id\": \"254...|          0|            1|                  |2025072300|\n",
      "|        0|          1|             0|            0|      0|           1|  1845991|         401|      |  27962|                  0|{\"strategy_id\": 1...|com.lenovo.anysha...|5a0bac80-6060-41e...|com.lenovo.anysha...|      1|4062155|          |80f14cce380246b08...|  110000|      4|     [0]|     21450|         null|        0|       0|     eg|     32|  23579487|          1|               |          0|       null|    null|   1009|  null|               midas|    381|prod|        0|          3|2a8aaacd-1b9e-449...|      |      |     eg|       eg|      1|      null|        1|     null|     0|          0|        0|     0|       0|INFINIX MOBILITY ...|         2.0|            0|        2|          |        |      0|{auto_active -> 0...|com.downlaoder.vi...|b26cc659-99a4-499...|1752451201000|  1752451201000|         0|1741432893|                 4|                      0|      0|4060014|          0|        15|      null|         null|      null|       null|[{\"test_id\": \"254...|          2|            1|                  |2025072300|\n",
      "|        0|          1|             0|            0|      0|           1|  1844343|         401|      |  26890|                  0|{\"strategy_id\": 1...|com.lenovo.anysha...|5a0bac80-6060-41e...|com.lenovo.anysha...|      1|4062558|          |8adc1333cfb440cb8...|   10000|      4|     [0]|     20494|         null|        0|       0|     mr|     32|  23565946|          0|               |          0|       null|    null|   1009|  null|               midas|     24|prod|        0|          1|00000000-0000-000...|      |      |     mr|       mr|      1|      null|        1|     null|     0|          0|        0|     0|       0|TECNO MOBILE LIMITED|         2.0|            0|        2|          |        |      0|{auto_active -> 0...|solitaire.puzzle....|99c3d2b2-a54d-43c...|1752451201000|  1752451201000|         0|1745799087|                 4|                      0|      0|4060041|          0|         0|      null|         null|      null|       null|[{\"test_id\": \"254...|          2|            2|                  |2025072300|\n",
      "|        0|          0|             0|            0|      0|           1|        0|        3507|      |   null|                  0|                  {}|com.lenovo.anysha...|5a0bac80-6060-41e...|com.lenovo.anysha...|      1|4065218|          |77141a62a61c468fb...|       0|      0|     [0]|      null|            1|        0|       0|       |       |      null|          0|       11627013|           |ad_exchange|    3507|   1006|  null|  ad_exchange:criteo| 940610|prod|        0|          0|e8bfdc28-c642-4f0...|      |      |     la|       la|      2|      null|        0|     null|     0|          0|     null|     0|       0|                vivo|         2.0|            0|        0|          |    null|   null|                  {}|                null|          sdRTJoFTYH|1752451201000|  1752451201000|         0|         0|                 0|                      0|      0|4060045|          0|      null|      null|         null|      null|       null|[{\"test_id\": \"254...|          0|            2|a.c427a9d894e8afb4|2025072300|\n",
      "|        0|          1|             0|            0|      0|           1|  1844343|        3496|      |  26890|                  0|{\"strategy_id\": 1...|com.lenovo.anysha...|5a0bac80-6060-41e...|com.lenovo.anysha...|      1|4062468|          |2de4c93ee0bd43bd8...|   12000|      4|     [0]|     20494|         null|        0|       0|     pe|     32|  23565946|          0|               |          0|       null|    null|   1009|  null|               midas|     10|prod|        0|          1|49d42065-062f-4c5...|      |      |     pe|       pe|      1|      null|        1|     null|     0|          0|        0|     0|       0|            motorola|         2.0|            0|        2|          |        |      0|{auto_active -> 0...|solitaire.puzzle....|972ecd13-b15e-4a3...|1752451201000|  1752451201000|         0|1750466428|                 4|                      0|      0|4060041|          0|         0|      null|         null|      null|       null|[{\"test_id\": \"254...|          2|            2|                  |2025072300|\n",
      "|        0|          0|             0|            0|      0|           1|        0|        2871|      |   null|                  0|                  {}|com.lenovo.anysha...|5a0bac80-6060-41e...|com.lenovo.anysha...|      1|4062599|          |907278468bff4fa2b...|       0|      0|     [0]|      null|            1|        0|       0|       |       |      null|          0|         789600|     216848|ad_exchange|    2871|   1006|  null|ad_exchange:pubna...| 147000|prod|        0|          0|d2218838-b1b6-4a4...|      |      |     pk|       pk|      1|      null|        0|     null|     0|          0|     null|     0|       0|            motorola|         2.0|            0|        0|          |    null|   null|                  {}|                null|2ba0d96d-731d-41a...|1752451202000|  1752451202000|         0|         0|                 0|                      0|      0|4060045|          0|      null|      null|         null|      null|       null|[{\"test_id\": \"254...|          0|            2|                  |2025072300|\n",
      "|        0|          1|             0|            0|      0|           1|  1844343|         401|      |  26890|                  0|{\"strategy_id\": 1...|com.lenovo.anysha...|5a0bac80-6060-41e...|com.lenovo.anysha...|      1|4062389|          |51532bdc00cb4120a...|   10000|      4|     [0]|     20494|         null|        0|       0|     mr|     64|  23565956|          0|               |          0|       null|    null|   1009|  null|               midas|     25|prod|        0|          1|1a51426f-a88c-474...|      |      |     mr|       mr|      1|      null|        1|     null|     0|          0|        0|     0|       0|              Xiaomi|         2.0|            0|        2|          |        |      0|{auto_active -> 0...|solitaire.puzzle....|7199ea00-b859-458...|1752451202000|  1752451202000|         0|1746189909|                 4|                      0|      0|4060029|          0|         0|      null|         null|      null|       null|[{\"test_id\": \"254...|          2|            2|                  |2025072300|\n",
      "+---------+-----------+--------------+-------------+-------+------------+---------+------------+------+-------+-------------------+--------------------+--------------------+--------------------+--------------------+-------+-------+----------+--------------------+--------+-------+--------+----------+-------------+---------+--------+-------+-------+----------+-----------+---------------+-----------+-----------+--------+-------+------+--------------------+-------+----+---------+-----------+--------------------+------+------+-------+---------+-------+----------+---------+---------+------+-----------+---------+------+--------+--------------------+------------+-------------+---------+----------+--------+-------+--------------------+--------------------+--------------------+-------------+---------------+----------+----------+------------------+-----------------------+-------+-------+-----------+----------+----------+-------------+----------+-----------+--------------------+-----------+-------------+------------------+----------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                "
     ]
    }
   ],
   "source": [
    "# 添加dt列，值为20250730\n",
    "df_with_dt = df.withColumn(\"dt\", lit(\"2025072300\"))\n",
    "# 显示结果\n",
    "df_with_dt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "d6cf5133-5e7d-4d6b-aec0-51e041314b34",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+\n",
      "|        dt|\n",
      "+----------+\n",
      "|2025072300|\n",
      "|2025072300|\n",
      "|2025072300|\n",
      "|2025072300|\n",
      "|2025072300|\n",
      "|2025072300|\n",
      "|2025072300|\n",
      "|2025072300|\n",
      "|2025072300|\n",
      "|2025072300|\n",
      "|2025072300|\n",
      "|2025072300|\n",
      "|2025072300|\n",
      "|2025072300|\n",
      "|2025072300|\n",
      "|2025072300|\n",
      "|2025072300|\n",
      "|2025072300|\n",
      "|2025072300|\n",
      "|2025072300|\n",
      "+----------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df_with_dt.select('dt').show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "6168da46-38c2-4c6c-8f2f-2ec15d4795c0",
   "metadata": {},
   "outputs": [],
   "source": [
    "pkg_list=[\n",
    "'com.loan.cash.credit.easy.kilat.cepat.pinjam.uang.dana.rupiah', # opay \n",
    "'com.adakami.dana.kredit.pinjaman'\n",
    "]\n",
    "opay_pkg='\\',\\''.join(pkg_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "f533b40c-50d9-48bc-97d6-27c62c8e3249",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "\"com.loan.cash.credit.easy.kilat.cepat.pinjam.uang.dana.rupiah','com.adakami.dana.kredit.pinjaman\""
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "opay_pkg"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "e1359fa1-6c51-4387-b346-a669c2e9e169",
   "metadata": {},
   "outputs": [],
   "source": [
    "pkg_list=[\n",
    "'com.loan.cash.credit.easy.kilat.cepat.pinjam.uang.dana.rupiah', # opay \n",
    "'com.adakami.dana.kredit.pinjaman'\n",
    "]\n",
    "opay_pkg='\\',\\''.join(pkg_list)\n",
    "pkg_condition='pkg_name in (\\'{}\\')'.format(opay_pkg)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "bf599ee4-6882-4d50-bcd1-d57b83f41c9d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "\"pkg_name in ('com.loan.cash.credit.easy.kilat.cepat.pinjam.uang.dana.rupiah','com.adakami.dana.kredit.pinjaman')\""
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pkg_condition"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "e533560a-e79c-4d58-bb03-147ab1788875",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "\"pkg_name in ('com.loan.cash.credit.easy.kilat.cepat.pinjam.uang.dana.rupiah','com.adakami.dana.kredit.pinjaman')\""
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "25/07/30 22:48:15 WARN HeartbeatReceiver: Removing executor driver with no recent heartbeats: 964328 ms exceeds timeout 120000 ms\n",
      "25/07/30 22:48:15 WARN SparkContext: Killing executors is not supported by current scheduler.\n"
     ]
    }
   ],
   "source": [
    "'pkg_name in (\\'{}\\')'.format('\\',\\''.join(pkg_list))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "22c7f542-087c-44ac-b62b-7e62fe172d62",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------------+-------------------+-------------------+\n",
      "|                 c1|                 c2|                 c3|\n",
      "+-------------------+-------------------+-------------------+\n",
      "|2025-07-31 11:00:00|2025-07-31 12:00:01|2025-07-31 12:00:01|\n",
      "+-------------------+-------------------+-------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "spark.sql(\"\"\"\n",
    "select \n",
    "from_unixtime(unix_timestamp('2025073111', 'yyyyMMddHH'), 'yyyy-MM-dd HH:mm:ss') c1\n",
    ",from_unixtime(1753934401, 'yyyy-MM-dd HH:mm:ss') c2\n",
    ",from_unixtime(1753934401000/1000, 'yyyy-MM-dd HH:mm:ss') c3\n",
    "\"\"\").show()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "5a8a4fb3-34dd-4020-976f-f06142c44f8b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------------------------------------------------------------------------------------------------+\n",
      "|from_unixtime(CAST((CAST(1753934401000 AS DOUBLE) / CAST(1000 AS DOUBLE)) AS BIGINT), yyyyMMddHHmmss)|\n",
      "+-----------------------------------------------------------------------------------------------------+\n",
      "|                                                                                       20250731120001|\n",
      "+-----------------------------------------------------------------------------------------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# 2025 0730 03 44 25\n",
    "spark.sql(\"\"\"\n",
    "select \n",
    "from_unixtime(1753934401000/1000, 'yyyyMMddHHmmss') \n",
    "\"\"\").show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "4e4b5849-24dd-4168-b4c0-7066609fc7a6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------+-----+------------+----+---+---+\n",
      "|  gaid|  pkg|receive_time|flag| rn| dr|\n",
      "+------+-----+------------+----+---+---+\n",
      "|user_a|pkg_1|  1753961125| ==1|  1|  1|\n",
      "|user_a|pkg_1|  1753961124| ==1|  2|  1|\n",
      "|user_a|pkg_1|  1753961122| ==1|  3|  1|\n",
      "|user_a|pkg_1|  1753961125| ==2|  4|  2|\n",
      "|user_a|pkg_1|  1753961124| ==2|  5|  2|\n",
      "+------+-----+------------+----+---+---+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "sql=\"\"\"\n",
    "with t_data as(\n",
    "select 'user_a' gaid,'pkg_1' pkg,'==1' flag,1753961122 receive_time\n",
    "union all\n",
    "select 'user_a' gaid,'pkg_1' pkg,'==1' flag,1753961125 receive_time\n",
    "union all\n",
    "select 'user_a' gaid,'pkg_1' pkg,'==1' flag,1753961124 receive_time\n",
    "union all\n",
    "select 'user_a' gaid,'pkg_1' pkg,'==2' flag,1753961125 receive_time\n",
    "union all\n",
    "select 'user_a' gaid,'pkg_1' pkg,'==2' flag,1753961124 receive_time\n",
    ")\n",
    "select \n",
    "   gaid\n",
    "   ,pkg\n",
    "   ,receive_time\n",
    "   ,flag\n",
    "   ,row_number() over(partition by gaid,pkg order by flag,receive_time desc) rn\n",
    "   ,dense_rank() over(partition by gaid,pkg order by flag) dr\n",
    "from t_data\n",
    "\"\"\"\n",
    "spark.sql(sql).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "664e7a38-93d1-4487-bdd4-11446f75235a",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.13.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
